﻿using Furion.DatabaseAccessor;
using FurStudy.Core;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FurStudy.Application
{
    /// <summary>
    /// 简单定义一个Sql代理接口
    /// 一旦这个接口继承了ISqlDispatchProxy，那么它就会动态创建接口实例，而且支持依赖注入/控制反转获取实例
    /// 在接口中贴[SqlDbContextLocator]特性，此方式下，接口所有方法将采用指定的数据库执行
    /// </summary>
    [SqlDbContextLocator(typeof(MySqlDbContextLocator))]
    [Timeout(1000)]
    public interface ISql : ISqlDispatchProxy
    {
        #region Sql操作

        #region 返回DataTable

        /// <summary>
        /// 执行sql并传入参数，基元类型
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        [SqlExecute("select * from person where id > @id and name like @name")]
        DataTable GetPerson(int id, string name);

        /// <summary>
        /// 执行sql并传入参数，对象类型
        /// </summary>
        /// <param name="paras"></param>
        /// <returns></returns>
        [SqlExecute("select * from person where id >@id and name like @name")]
        DataTable GetPerson(MyParam paras);

        /// <summary>
        /// 执行存储过程sql，支持设置参数类型
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [SqlExecute("exec PROP_NAME @id", CommandType = CommandType.StoredProcedure)]
        DataTable GetPerson(int id);

        /// <summary>
        /// 支持多数据库操作
        /// </summary>
        /// <returns></returns>
        [SqlExecute("select * from person"), SqlDbContextLocator(typeof(MySqlDbContextLocator))]
        DataTable GetPerson();

        /// <summary>
        /// 异步方式
        /// </summary>
        /// <returns></returns>
        [SqlExecute("select * from person"), SqlDbContextLocator(typeof(MySqlDbContextLocator))]
        Task<DataTable> GetPersonAsync();

        #endregion

        #region 返回List<T>

        /// <summary>
        /// // 执行sql并传入参数，基元类型
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        [SqlExecute("select * from person where id >@id and name like @name")]
        List<Person> GetPersonList(int id, string name);

        /// <summary>
        /// 执行sql并传入参数，对象类型
        /// </summary>
        /// <param name="paras"></param>
        /// <returns></returns>
        [SqlExecute("select * from person where id >@id and name like @name")]
        List<Person> GetPersonList(MyParam paras);

        /// <summary>
        /// 执行存储过程 sql，支持设置参数类型
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [SqlExecute("exec PROP_NAME @id", CommandType = CommandType.StoredProcedure)]
        List<Person> GetPersonList(int id);

        /// <summary>
        /// 支持多数据库操作
        /// </summary>
        /// <returns></returns>
        [SqlExecute("select * from person"), SqlDbContextLocator(typeof(MySqlDbContextLocator))]
        List<Person> GetPersonList();

        /// <summary>
        /// 异步方式
        /// </summary>
        /// <returns></returns>
        [SqlExecute("select * from person"), SqlDbContextLocator(typeof(MySqlDbContextLocator))]
        Task<List<Person>> GetPersonListAsync();

        #endregion

        #region 返回DataSet

        /// <summary>
        /// // 执行sql并传入参数，基元类型
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        [SqlExecute(@"
            select * from person where id >@id and name like @name;
            select top 10 * from student where Id >@id;")]
        DataSet GetData(int id, string name);

        /// <summary>
        /// 执行sql并传入参数，对象类型
        /// </summary>
        /// <param name="paras"></param>
        /// <returns></returns>
        [SqlExecute(@"
            select * from person where id >@id and name like @name;
            select top 10 * from student where Id >@id;")]
        DataSet GetData(MyParam paras);

        /// <summary>
        /// 执行存储过程 sql，支持设置参数类型
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [SqlExecute(@"
            exec PROP_NAME @id;
            select * from person;", CommandType = CommandType.StoredProcedure)]
        DataSet GetData(int id);

        /// <summary>
        /// 支持多数据库操作
        /// </summary>
        /// <returns></returns>
        [SqlExecute(@"
            select * from person;
            select * from student;"), SqlDbContextLocator(typeof(MySqlDbContextLocator))]
        DataSet GetData();

        /// <summary>
        /// 异步方式
        /// </summary>
        /// <returns></returns>
        [SqlExecute(@"
            select * from person;
            select * from student;
            select 1;"), SqlDbContextLocator(typeof(MySqlDbContextLocator))]
        Task<DataSet> GetDataAsync();

        #endregion

        #region 返回Tuple<T1,...T8>

        /// <summary>
        /// 执行sql并传入参数，基元类型
        /// </summary>
        /// <param name="id"></param> 
        /// <param name="name"></param>
        /// <returns></returns>
        [SqlExecute(@"
            select * from person where id >@id and name like @name;
            select top 10 * from student where Id >@id;")]
        (List<Person>, List<Children>) GetTupleData(int id, string name);

        /// <summary>
        /// 执行sql并传入参数，对象类型
        /// </summary>
        /// <param name="paras"></param>
        /// <returns></returns>
        [SqlExecute(@"
            select * from person where id >@id and name like @name;
            select top 10 * from student where Id >@id;")]
        (List<Person>, List<Children>) GetTupleData(MyParam paras);

        /// <summary>
        /// 执行存储过程 sql，支持设置参数类型
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [SqlExecute(@"
            exec PROP_NAME @id;
            select * from person;", CommandType = CommandType.StoredProcedure)]
        (List<Person>, List<Children>) GetTupleData(int id);

        /// <summary>
        /// 支持多数据库操作
        /// </summary>
        /// <returns></returns>
        [SqlExecute(@"
            select * from person;
            select * from student;"), SqlDbContextLocator(typeof(MySqlDbContextLocator))]
        (List<Person>, List<Children>) GetGetTupleDataData();

        /// <summary>
        /// 异步方式
        /// </summary>
        /// <returns></returns>
        [SqlExecute(@"
            select * from person;
            select * from student;
            select 1;"), SqlDbContextLocator(typeof(MySqlDbContextLocator))]
        Task<(List<Person>, List<Children>, List<int>)> GetTupleDataAsync();

        #endregion

        #region 返回单行单列

        [SqlExecute("select Name from person where id = @id")]
        string GetStringValue(int id);

        [SqlExecute("select age from person where id = @id")]
        int GetIntValue(int id);

        [SqlExecute("select Name from person where id = @id")]
        Task<string> GetStringValueAsync(int id);

        #endregion

        #region 无返回值

        [SqlExecute("insert into person(Name,Age) values(@name, @age)")]
        void Insert(MyParam dto);

        [SqlExecute("delete from person where id = @id")]
        void Delete(int id);

        [SqlExecute("update person set name=@name where id=@id")]
        void Update(int id, string name);

        #endregion

        #endregion

        #region 存储过程操作

        #region 返回DataTable

        [SqlProcedure("PROC_Name")]
        DataTable GetPersons(MyParam dto);

        [SqlProcedure("PROC_Name")]
        DataTable GetPersons(int id);

        [SqlProcedure("PROC_Name")]
        DataTable GetPersons(int id, string name);

        #endregion

        #region 返回List<T>

        [SqlProcedure("PROC_Name")]
        List<Person> GetPersonsList(MyParam dto);

        [SqlProcedure("PROC_Name")]
        List<Person> GetPersonsList(int id);

        [SqlProcedure("PROC_Name")]
        List<Person> GetPersonsList(int id, string name);

        #endregion

        #region 返回DataSet

        [SqlProcedure("PROC_Name")]
        DataSet GetProcData(MyParam dto);

        [SqlProcedure("PROC_Name")]
        DataSet GetProcData(int id);

        [SqlProcedure("PROC_Name")]
        DataSet GetProcData(int id, string name);

        #endregion

        #region 返回Tuple(T1,...T8)

        [SqlProcedure("PROC_Name")]
        (List<Person>, List<Children>) GetProcTupleData(MyParam dto);

        [SqlProcedure("PROC_Name")]
        (List<Person>, List<Children>) GetProcTupleData(int id);

        [SqlProcedure("PROC_Name")]
        (List<Person>, List<Children>, Person, int) GetProcTupleData(int id, string name);

        #endregion

        #region 返回单行单列

        [SqlProcedure("PROC_Name")]
        object GetValue(MyParam dto);

        [SqlProcedure("PROC_Name")]
        string GetValue(int id);

        [SqlProcedure("PROC_Name")]
        int GetValue(int id, string name);

        #endregion

        #region 无返回值

        [SqlProcedure("PROC_Name")]
        void GetProcValue(MyParam dto);

        [SqlProcedure("PROC_Name")]
        void GetProcValue(int id);

        [SqlProcedure("PROC_Name")]
        void GetProcValue(int id, string name);

        #region 带OUTPUT/RETURN返回

        [SqlProcedure("PROC_Name")]
        ProcedureOutputResult GetOutput(ProcOutputModel pams);

        [SqlProcedure("PROC_Name")]
        ProcedureOutputResult<(List<Person>, List<Children>)> GetReturnOutput(ProcOutputModel pams);

        #endregion

        #endregion

        #endregion

        #region 函数操作

        // Sql代理会自动判断返回值然后自动执行特定函数类型

        [SqlFunction("FN_Name")] // 标量函数
        string GetFunctionValue(MyParam dto);

        [SqlProcedure("FN_Name")]   // 表值函数
        List<Person> GetFunctionPersons(int id);

        #endregion

        #region Sql模板替换

        [SqlExecute("select * from person where id > {id} and name like {name} and age > {user.Age}")]
        List<Person> GetPerson(int id, string name, User user);

        // 两者区别：模板字符串有别于命令参数替换，模板字符串采用{ }方式，运行时直接替换为实际的内容，@而是转换成DbParameter参数

        #endregion

        #region 切换数据库

        #region 单个方法方式

        /// <summary>
        /// 主要通过在方法上贴[SqlDbContextLocator]特性
        /// </summary>
        /// <returns></returns>
        [SqlExecute("select * from person"), SqlDbContextLocator(typeof(MySqlDbContextLocator))]
        List<Person> GetDBPerson();

        #endregion

        #region 接口方式

        // 见接口上方

        #endregion

        #region 运行时 .change方法切换

        // 将 sql 代理数据库切换成特定数据库
        //_sql.Change<MySqlDbContextLocator>();
        //_sql.GetPerson();

        // 多次切换
        //_sql.Change<OracleDbContextLocator>();
        //_sql.GetPerson();

        // 还支持重置数据库上下文定位器为初始状态
        //_sql.ResetIt();
        //_sql.GetPerson();

        #endregion

        // 关于优先级问题：
        // .Change<> 优先级大于 方法贴 [SqlDbContextLocator] 大于 接口贴 [SqlDbContextLocator]。
        // 默认情况下，不指定 DbContextLocator 属性，则为 MasterDbContextLocator

        #endregion

        #region Sql代理拦截

        // 在 Furion v2.13 +版本新增了Sql代理拦截功能，可以篡改特定方法或所有代理方法实际执行的参数，如 sql语句、参数、执行对象等等。
        // 若在Sql代理中实现拦截功能，必须满足两个条件：
        // 方法必须是static静态方法且返回值为void且只有一个SqlProxyMethod参数
        // 方法必须贴[Interceptor]特性

        [SqlExecute("select name from person",InterceptorId = "GetPersonsByName")] // 通过InterceptorId解决方法名重载问题
        Task<List<string>> GetPerons();

        /// <summary>
        /// 只拦截GetValue方法
        /// </summary>
        /// <param name="method"></param>
        [Interceptor(nameof(GetValue))]
        static void Intercept1(SqlProxyMethod method)
        {
            method.FinalSql += " where id > 1"; // 篡改最终执行sql
        }

        /// <summary>
        /// 拦截GetValue和GetPersons方法
        /// </summary>
        /// <param name="method"></param>
        [Interceptor(nameof(GetValue), nameof(GetPersons))]
        static void Intercept2(SqlProxyMethod method)
        {
            method.FinalSql += " where id > 1"; // 篡改最终执行 sql
        }

        [Interceptor("GetPersonsByName")]   // 对应上面的 InterceptorId 配置
        static void 解决方法名重载拦截(SqlProxyMethod method)
        {
            // 。。。
        }

        [Interceptor]
        static void 全局拦截(SqlProxyMethod method)
        {
            // 这里会拦截所有的方法
        }

        #endregion

        #region 设置超时时间

        [SqlFunction("FN_Name"), Timeout(500)] // 单位秒
        string GetSetTimeOutValue(MyParam dto);

        #endregion
    }
}
